package merlin.dao;

import java.util.*;
import java.sql.*;

import merlin.beans.*;
import merlin.utils.ApplicationResources;

public class FinancialDetailsDAO implements java.io.Serializable{

	Connection con;

	private static Map mapFinancialCriteria = new HashMap();

	static{

		mapFinancialCriteria.put(1,"CCD_COUNTY");
		mapFinancialCriteria.put(2,"CCD_NUMBER_OF_EMPLOYEES");
		mapFinancialCriteria.put(3,"CCD_TOTAL_TANGIBLE_ASSETS");
		mapFinancialCriteria.put(4,"CCD_TOTAL_FIXED_ASSETS");
		mapFinancialCriteria.put(5,"CCD_TOTAL_CURRENT_ASSETS");
		mapFinancialCriteria.put(6,"CCD_TOTAL_CURRENT_LIABILITIES");
		mapFinancialCriteria.put(7,"CCD_WORKING_CAPITAL");
		mapFinancialCriteria.put(8,"CCD_NET_ASSETS");
		mapFinancialCriteria.put(9,"CCD_LONG_TERM_LOANS");
		mapFinancialCriteria.put(10,"CCD_OTHER_LONG_TERM_LIABILITIES");
		mapFinancialCriteria.put(11,"CCD_SHAREHOLDER_FUNDS");
		mapFinancialCriteria.put(12,"CCD_TURNOVER");
		mapFinancialCriteria.put(13,"CCD_GROSS_PROFIT");
		mapFinancialCriteria.put(14,"CCD_OPERATING_PROFIT");
		mapFinancialCriteria.put(15,"CCD_PROFIT_BEFORE_INTEREST_AND_TAX");
		mapFinancialCriteria.put(16,"CCD_PRE_TAX_PROFIT");
		mapFinancialCriteria.put(17,"CCD_PROFIT_AFTER_TAX");
		mapFinancialCriteria.put(18,"CCD_RETAINED_PROFIT");
		mapFinancialCriteria.put(19,"CCD_ESTIMATED_TURNOVER");
		mapFinancialCriteria.put(20,"CCD_DATE_OF_INCORPORATION");
		mapFinancialCriteria.put(21,"CCD_GROSS_PROFIT_MARGIN");
		mapFinancialCriteria.put(22,"CCD_OPERATING_PROFIT_MARGIN");
		mapFinancialCriteria.put(23,"CCD_PROFIT_BEFORE_INTEREST_AND_TAX_MARGIN");
		mapFinancialCriteria.put(24,"CCD_PRE_TAX_PROFIT_MARGIN");
		mapFinancialCriteria.put(25,"CCD_RETURN_ON_TOTAL_ASSETS");
		mapFinancialCriteria.put(26,"CCD_RETURN_ON_CAPITAL_EMPLOYED");
		mapFinancialCriteria.put(27,"CCD_RETURN_ON_EQUITY");
		mapFinancialCriteria.put(28,"CCD_CURRENT_RATIO");
		mapFinancialCriteria.put(29,"CCD_QUICK_RATIO");
		mapFinancialCriteria.put(30,"CCD_DEBT_RATIO");
		mapFinancialCriteria.put(31,"CCD_CREDIT_GIVEN");
		mapFinancialCriteria.put(32,"CCD_CREDIT_TAKEN");
		mapFinancialCriteria.put(33,"CCD_TIMES_INTEREST_EARNED");
		mapFinancialCriteria.put(34,"CCD_FIXED_ASSET_TURNOVER");
		mapFinancialCriteria.put(35,"CCD_STOCK_TURNOVER");
		mapFinancialCriteria.put(36,"CCD_DEBTOR_TURNOVER");
		mapFinancialCriteria.put(37,"CCD_SALES_PER_EMPLOYEE");
		mapFinancialCriteria.put(38,"CCD_AVERAGE_WAGE");
		mapFinancialCriteria.put(39,"CCD_GROWTH_IN_TURNOVER");
		mapFinancialCriteria.put(40,"CCD_GROWTH_IN_OPERATING_PROFIT");
		mapFinancialCriteria.put(41,"CCD_GROWTH_IN_PRE_TAX_PROFIT");
		mapFinancialCriteria.put(42,"CCD_GROWTH_IN_EMPLOYEES");
		mapFinancialCriteria.put(43,"CCD_REGION");


		/*
		0	Select Data Here
		1	County
		2	Number of Employees
		3	Total Tangible Assets
		4	Total Fixed Assets
		5	Total Current Assets
		6	Total Current Liabilities
		7	Working Capital
		8	Net Assets
		9	Long Term Loans
		10	Other Long Term Liabilities
		11	Shareholder Funds
		12	Turnover
		13	Gross Profit
		14	Operating Profit
		15	Profit Before Interest & Tax
		16	Pre-tax Profit
		17	Profit After Tax
		18	Retained Profit
		19  Estimated Turnover
		20  Incorporation Date
		21	Gross Profit Margin
		22	Operating Profit Margin
		23	Profit Before Interest & Tax Margin
		24	Pre-tax Profit Margin
		25	Return On Total Assets
		26	Return On Capital Employed
		27	Return On Equity
		28	Current Ratio
		29	Quick Ratio
		30	Debt Ratio
		31	Credit Given
		32	Credit Taken
		33	Times Interest Earned
		34	Fixed Asset Turnover
		35	Stock Turnover
		36	Debtor Turnover
		37	Sales Per Employee
		38	Average Wage
		39	Growth In Turnover
		40	Growth In Operating Profit
		41	Growth In Pre-tax Profit
		42	Growth In Employees
		43	Region
		*/

	}

	public FinancialDetailsDAO(Connection con){
		this.con = con;
	}

	public FinancialDetailsBean selectFinancialDetailsByPK(String pk)throws SQLException{

		PreparedStatement pstmt = con.prepareStatement("SELECT COMPANY_NUMBER,YEAR1,YEAR2,YEAR3,YEAR4,FD_ORDER FROM FINANCIAL_DETAILS WHERE COMPANY_NUMBER = ? ");
		pstmt.setString(1,pk);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			FinancialDetailsBean bn = new FinancialDetailsBean();
			bn.setCompanyNumber(rs.getString("COMPANY_NUMBER"));
			bn.setYear1(rs.getString("YEAR1"));
			bn.setYear2(rs.getString("YEAR2"));
			bn.setYear3(rs.getString("YEAR3"));
			bn.setYear4(rs.getString("YEAR4"));
			bn.setFdOrder(rs.getInt("FD_ORDER"));
			return bn;
		}else{
			return null;
		}
	}

	public DbResultSet selectFinancialDetailsRecordsByCompanyNumber(String companyNumber)throws SQLException{
		ArrayList records = new ArrayList();
		PreparedStatement pstmt = con.prepareStatement("SELECT FD.COMPANY_NUMBER, FDD.FDD_ORDER, FDD.DESCRIPTION, FD.YEAR1, FD.YEAR2, FD.YEAR3, FD.YEAR4 FROM NSD.FINANCIAL_DETAILS FD, NSD.FINANCIAL_DETAILS_DESCRIPTION FDD WHERE FD.FD_ORDER = FDD.FDD_ORDER AND COMPANY_NUMBER =  ? ORDER BY FDD.FDD_ORDER");
		pstmt.setString(1,companyNumber);
		ResultSet rs =  pstmt.executeQuery();
		while(rs.next()){
			FinancialDetailsBean bn = new FinancialDetailsBean();
			bn.setCompanyNumber(rs.getString("COMPANY_NUMBER"));
			bn.setDescription(rs.getString("DESCRIPTION"));
			bn.setYear1(rs.getString("YEAR1"));
			bn.setYear2(rs.getString("YEAR2"));
			bn.setYear3(rs.getString("YEAR3"));
			bn.setYear4(rs.getString("YEAR4"));
			records.add(bn);
		}
		rs.close();
		pstmt.close();
		if(!records.isEmpty())return new DbResultSet(records,records.size()); else return new DbResultSet();
	}

	/**
	 * Function for the Free Updates Custom Reports Creation Tab.
	 *
	 * @param reportId				The report number for which companies are to be retrieved.
	 * @param financialTypeId		The financial criteria type for which the data is to be retrieved.
	 * @param lowerRange			Range selection parameters, may be null
	 * @param upperRange            Range selection parameters, may be null
	 * @param companiesToInclude	Filter to select data only for these companies, may be null
	 * @param pageNo				The page offset in case the report is too large and has been paginated on the client side.
	 */
	public List selectFinancialDetailsRecordsByReportAndFinancialType(int reportId, int financialTypeId,String lowerRange, String upperRange, String companiesToInclude, int pageNo)throws SQLException{
		List records = new ArrayList();

		System.out.println("reportId " + reportId);

		// if the report id is bigger than 1000000, then it is a custom report, and the
		// company ids are to be fetched from MSAL_CUSTOMISED_REPORTS table.
		String sql = null;

		String finCriteriaFld = (String)mapFinancialCriteria.get(financialTypeId);
		sql = "SELECT ";
		// pagination required ? 
		if((companiesToInclude  == null || companiesToInclude.trim().length() == 0) && (pageNo > 0)){
			int pageSize = Integer.parseInt(ApplicationResources.getInstance().getProperty("reportChunkSize"));
			pageNo--;
			int start = (pageNo * pageSize);
			sql += " TOP(" + start + "," + pageSize + ") ";
		}
		sql += "CCD_REGISTERED_NUMBER,CCD_COMPANY_NAME, " + finCriteriaFld +
			" FROM COMPANY_COMPARISON_DATA, MSAL_REPORTS " +
			" WHERE CCD_REGISTERED_NUMBER = MSAL_R_REGISTERED_NUMBER AND MSAL_R_REPORT_NUMBER = ? ";

		// If the financial type is 1, then it is county. Put single quotes around the value,
		// and use a IN clause.
        if(financialTypeId == 1){
        	String inData = "";
			if(lowerRange != null && lowerRange.length() > 0){
	        	inData += ",'" + lowerRange + "'";
	        }

	        if(upperRange != null && upperRange.length() > 0){
	        	inData += ",'" + upperRange + "'";
	        }
	        if(inData.length() > 0){
	        	sql += " AND " + finCriteriaFld + " IN (" + inData.substring(1) + ") ";
	        }
        }else{
			if(lowerRange != null && lowerRange.length() > 0){

	        	sql += " AND " + finCriteriaFld + " >= " + lowerRange + " ";
	        }

	        if(upperRange != null && upperRange.length() > 0){
	        	sql += " AND " + finCriteriaFld + " <= " + upperRange + " ";
	        }
		}
        if(companiesToInclude  != null && companiesToInclude.length() > 0){
			sql += " AND CCD_REGISTERED_NUMBER IN(" + companiesToInclude + ") ";
        }
        sql += "ORDER BY CCD_COMPANY_NAME ASC";

		System.out.println("selectFinancialDetailsRecordsByReportAndFinancialType : " + sql);
		System.out.println("with report id " + reportId + " financialTypeId " + finCriteriaFld);

		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1,reportId);

		long startTime = System.currentTimeMillis();

		ResultSet rs =  pstmt.executeQuery();
		while(rs.next()){
			FinancialDetailsBean bn = new FinancialDetailsBean();
			bn.setCompanyNumber(rs.getString("CCD_REGISTERED_NUMBER"));
			bn.setCompanyName(rs.getString("CCD_COMPANY_NAME"));
			bn.setYear1(rs.getString(finCriteriaFld));

			records.add(bn);
		}
		rs.close();
		pstmt.close();

		System.out.println("time taken " + (System.currentTimeMillis() - startTime) + " milliseconds");

		return records;
	}


	public List selectFinancialDetailsRecordsByCompanyIdsAndFinancialType(int financialTypeId,String lowerRange, String upperRange, String companiesToInclude, int pageNo)throws SQLException{
		List records = new ArrayList();

		String sql = null;

		String finCriteriaFld = (String)mapFinancialCriteria.get(financialTypeId);
		
		sql = "SELECT ";
		// pagination required ? 
		if(pageNo > 0){
			int pageSize = Integer.parseInt(ApplicationResources.getInstance().getProperty("reportChunkSize"));
			pageNo--;
			int start = (pageNo * pageSize);
			sql += " TOP(" + start + "," + pageSize + ") ";
		}
		sql += " CCD_REGISTERED_NUMBER,CCD_COMPANY_NAME, " + finCriteriaFld +
			" FROM COMPANY_COMPARISON_DATA " +
			" WHERE 1 = 1 "; // to include the WHERE clause

        if(lowerRange != null && lowerRange.length() > 0){
        	sql += " AND " + finCriteriaFld + " >= " + lowerRange + " ";
        }

        if(upperRange != null && upperRange.length() > 0){
        	sql += " AND " + finCriteriaFld + " <= " + upperRange + " ";
        }

        if(companiesToInclude  != null && companiesToInclude.length() > 0){
			sql += " AND CCD_REGISTERED_NUMBER IN(" + companiesToInclude + ") ";
        }
        sql += "ORDER BY CCD_COMPANY_NAME ASC";


		System.out.println("selectFinancialDetailsRecordsByCompanyIdsAndFinancialType : " + sql);
		System.out.println("with financialTypeId " + financialTypeId);

		PreparedStatement pstmt = con.prepareStatement(sql);

		ResultSet rs =  pstmt.executeQuery();
		while(rs.next()){
			FinancialDetailsBean bn = new FinancialDetailsBean();
			bn.setCompanyNumber(rs.getString("CCD_REGISTERED_NUMBER"));
			bn.setCompanyName(rs.getString("CCD_COMPANY_NAME"));
			bn.setYear1(rs.getString(finCriteriaFld));

			records.add(bn);
		}
		rs.close();
		pstmt.close();

		return records;
	}
}